package org.jeecg.glr.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.sun.rmi.rmid.ExecOptionPermission;
import org.jeecg.common.constant.SysConstant;
import org.jeecg.common.util.SysUtil;
import org.jeecg.glr.api.constant.ApiConstant;
import org.jeecg.glr.entity.GlrLightCurrent;
import org.jeecg.glr.entity.GlrLightData;
import org.jeecg.glr.mapper.GlrLightCurrentMapper;
import org.jeecg.glr.mapper.GlrLightDataMapper;
import org.jeecg.glr.mapper.GlrVideoYsMapper;
import org.jeecg.glr.service.IGlrLightCurrentService;
import org.jeecg.glr.service.IGlrLightDataService;
import org.jeecg.glr.util.DcDateUtils;
import org.jeecg.glr.util.StringUtil;
import org.jeecg.glr.util.SunRiseSet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static java.lang.Math.round;

/**
 * @Description: 路灯网关统计数据
 * @Author: jeecg-boot
 * @Date:   2020-03-12
 * @Version: V1.0
 */
@Service
public class GlrLightDataServiceImpl extends ServiceImpl<GlrLightDataMapper, GlrLightData> implements IGlrLightDataService {

    @Autowired
    private GlrVideoYsMapper glrVideoYsMapper;
    /**
     *
     * @param stDate
     */
    public void doSaveDate(String stDate){
        if (stDate.length()!=10){
            return;
        }
        String sqlOne = "select \n" +
                "'"+stDate+"' as createDate,\n" +
                "a.lig_code,\n" +
                "avg(a.current_power) avg_power,\n" +
                "avg(a.current_power) rated_power,\n" +
                "avg(a.current_apparent_power) current_apparent_power,\n" +
                "avg(a.current_vol) avg_vol,\n" +
                "avg(a.current_cur) avg_cur,\n" +
                "avg(a.current_brightness) avg_Brightness,\n" +
                "avg(a.current_temperature) avg_temperature,\n" +
                "a.sys_company_code ,\n" +
                "a.gata_way_code,\n" +
                "e.pattern_name,\n" +
                "e.type as pattern_type,\n" +
                "e.command_timea,e.bright_valuea,\n" +
                "e.typeb,e.timeb," +
                "e.command_timeb,e.bright_valueb,\n" +
                "e.typec,e.timec," +
                "e.command_timec,e.bright_valuec,\n" +
                "e.command_timed,e.bright_valued,\n" +
                "e.command_timee,e.bright_valuee,\n" +
                "f.latitude,\n" +
                "f.longitude\n" +
                "from glr_light_current a\n" +
                "left join glr_light b on a.lig_code=b.code and a.gata_way_code=b.gate_way_code\n" +
                "left join glr_light_type c on b.light_type_code=c.code\n" +
                "left join glr_pattern_gateway d on d.gate_way_code=a.gata_way_code and d.current_status='Y'\n" +
                "left join glr_pattern e on d.pattern_id=e.id\n" +
                "left join glr_gateway f on a.gata_way_code=f.gate_way_code\n" +
                "where date_format(a.create_time, '%Y-%m-%d')='"+stDate+"'  \n" +
                //"and ((a.current_cur<>0 and a.current_vol<>0) or (a.current_cur=0 and a.current_vol=0 and a.current_brightness=0 and a.current_temperature=0))\n" +
                "and a.current_cur<>0 and a.current_vol<>0\n" +
                "GROUP BY a.lig_code ,a.gata_way_code\n" +
                //"order by a.lig_code desc,a.sys_company_code  ";
                "order by a.gata_way_code,a.lig_code asc";

        //select DATE_SUB(curdate(),INTERVAL 1 DAY)
        //DATE_SUB(curdate(),INTERVAL 1 DAY)
        List<Map<String, Object>> list = glrVideoYsMapper.doGetListForJdbc(sqlOne);
        List<GlrLightData> dataList = new ArrayList<GlrLightData>();

        String sqlOne1 =" select \n" +
                " gata_way_code,\n" +
                " lig_code,\n" +
                " IFNULL(max(month_electricity),0) as sum_total_electricity,\n" +
                " IFNULL(max(month_economy_electricity),0) as sum_total_economy_Electricity\n" +
                " from glr_light_data \n" +
                " where Date(create_time) <= '"+stDate+"' \n" +
                " group by gata_way_code,lig_code";
        List<Map<String, Object>> listAll = glrVideoYsMapper.doGetListForJdbc(sqlOne1);

        for (Map<String, Object> map : list) {
            GlrLightData domain = new GlrLightData();
            domain.setCreateTime(DcDateUtils.toDate(map.get("createDate").toString(), "yyyy-MM-dd"));
            domain.setLigCode(map.get("lig_code").toString());
            String gata_way_code=map.get("gata_way_code").toString();

            if ("01012001150001".equals(gata_way_code)||"01012005270001".equals(gata_way_code)){
                System.out.println(gata_way_code);
            }

            domain.setGataWayCode(gata_way_code);
            String stName = "";

            stName = "rated_power"; //额定功率
            String rated_power = "0";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                rated_power = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(rated_power)){
                //continue;
                rated_power="100";
            }

            stName = "command_timea"; //时段1时间
            String command_timea = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                command_timea = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(command_timea)){
                //continue;
                command_timea="03:00";
            }

            stName = "latitude"; //经纬度，取日出时间
            String latitude = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                latitude = map.get(stName).toString();
            }

            stName = "longitude"; //经纬度，取日出时间
            String longitude = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                longitude = map.get(stName).toString();
            }

            BigDecimal b_longitude=new BigDecimal("0");
            BigDecimal b_latitude=new BigDecimal("0");
            try{
                b_longitude=new BigDecimal(longitude);
                b_latitude=new BigDecimal(latitude);
            }catch (Exception ex){

            }
            //如果找不到经纬度就设成默认经纬度值
            if ( b_longitude.compareTo(new BigDecimal("0"))<=0){
                b_longitude= new BigDecimal(ApiConstant.COMMON_LOCATION.get("longitude"));
            }

            if ( b_latitude.compareTo(new BigDecimal("0"))<=0){
                b_latitude= new BigDecimal(ApiConstant.COMMON_LOCATION.get("latitude"));
            }

            //日出时间
            String strSunrise = SunRiseSet.getSunrise(b_longitude,b_latitude,new Date());
            //日落时间
            String strSunset = SunRiseSet.getSunset(b_longitude,b_latitude,new Date());

            stName = "typeb"; //日出 00提前 01延迟
            String typeb= "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                typeb = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(typeb)){
                //continue;
                typeb="00";
            }

            stName = "timeb"; //15分钟
            String timeb = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                timeb = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(timeb)){
                //continue;
                timeb="15";
            }

            stName = "typec"; //日落 00提前 01延迟
            String typec = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                typec = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(typec)){
                //continue;
                typec="00";
            }

            stName = "timec"; //15分钟
            String timec = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                timec = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(timec)){
                //continue;
                timec="15";
            }

            stName = "command_timeb"; //日出多少分钟
            String command_timeb = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                command_timeb = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(command_timeb)){
                //continue;
                command_timeb="07:00";
            }

            String command_timeb1=addDateMinut(strSunrise,typeb,Integer.parseInt(timeb));

            stName = "command_timec"; //时段3
            String command_timec = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                command_timec = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(command_timec)){
                //continue;
                command_timec="18:30";
            }

            String command_timec1=addDateMinut(strSunset,typec,Integer.parseInt(timec));

            //0 关闭 1 开启自动时段
            if ("0501".equals(map.get("gata_way_code").toString().substring(0,4))&&"0".equals(map.get("pattern_type").toString())){
                command_timeb1=command_timeb;
                command_timec1=command_timec;
            }

            stName = "command_timed"; //时段4
            String command_timed = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                command_timed = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(command_timed)){
                //continue;
                command_timed="21:00";
            }

            stName = "command_timee";//时段5
            String command_timee = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                command_timee = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(command_timee)){
                //continue;
                command_timee="23:00";
            }

            stName = "bright_valuea";
            String bright_valuea = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                bright_valuea = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(bright_valuea)){
                //continue;
                bright_valuea="30";
            }

            stName = "bright_valueb";
            String bright_valueb = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                bright_valueb = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(bright_valueb)){
                //continue;
                bright_valueb="0";
            }

            stName = "bright_valuec";
            String bright_valuec = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                bright_valuec = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(bright_valuec)){
                //continue;
                bright_valuec="60";
            }

            stName = "bright_valued";
            String bright_valued = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                bright_valued = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(bright_valued)){
                //continue;
                bright_valued="40";
            }

            stName = "bright_valuee";
            String bright_valuee = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                bright_valuee = map.get(stName).toString();
            }
            if (StringUtil.isEmpty(bright_valuee)){
                //continue;
                bright_valuee="30";
            }

            stName = "current_apparent_power"; //视在功率 就是当前功率
            String current_apparent_power = "";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                current_apparent_power = map.get(stName).toString();
            }
            stName = "avg_temperature"; //温度
            String avg_temperature = "0";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                avg_temperature = map.get(stName).toString();
            }


            Map ss0 = computePower(command_timea, command_timeb1, bright_valuea, current_apparent_power, rated_power);
            Map ss1 = computePower(command_timeb1, command_timec1, bright_valueb, current_apparent_power, rated_power);
            Map ss2 = computePower(command_timec1, command_timed, bright_valuec, current_apparent_power, rated_power);
            Map ss3 = computePower(command_timed, command_timee, bright_valued, current_apparent_power, rated_power);
            Map ss4 = computePower(command_timee, command_timea, bright_valuee, current_apparent_power, rated_power);

            //计算实际用电
            BigDecimal dayElectricity = new BigDecimal(ss0.get("b_shiji").toString());
            dayElectricity = dayElectricity.add(new BigDecimal(ss1.get("b_shiji").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN);
            dayElectricity = dayElectricity.add(new BigDecimal(ss2.get("b_shiji").toString()).setScale(3,BigDecimal.ROUND_HALF_DOWN));
            dayElectricity = dayElectricity.add(new BigDecimal(ss3.get("b_shiji").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN);
            dayElectricity = dayElectricity.add(new BigDecimal(ss4.get("b_shiji").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN);

            //节约用电
            BigDecimal economyElectricity = new BigDecimal(ss0.get("b_jieyue").toString());
            economyElectricity = economyElectricity.add(new BigDecimal(ss1.get("b_jieyue").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN);
            economyElectricity = economyElectricity.add(new BigDecimal(ss2.get("b_jieyue").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN);
            economyElectricity = economyElectricity.add(new BigDecimal(ss3.get("b_jieyue").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN);
            economyElectricity = economyElectricity.add(new BigDecimal(ss4.get("b_jieyue").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN);


            if (dayElectricity.doubleValue()>1000){
                System.out.println(dayElectricity);
            }
            //当日用电量
            domain.setDayElectricity(dayElectricity.doubleValue());
            //当日节约电量
            domain.setDayEconomyElectricity(economyElectricity.doubleValue());

            for(Map<String, Object> mapAll : listAll){
                String st1=mapAll.get("lig_code").toString();
                String st2=mapAll.get("gata_way_code").toString();
                String st3=mapAll.get("sum_total_electricity").toString();
                String st4=mapAll.get("sum_total_economy_Electricity").toString();

                if (st1.equals(domain.getLigCode())&&st2.equals(domain.getGataWayCode())){
                    BigDecimal totalElectricity =dayElectricity.add(new BigDecimal(st3)).setScale(3,BigDecimal.ROUND_HALF_DOWN);
                    BigDecimal totalEconomyElectricity =economyElectricity.add(new BigDecimal(st4)).setScale(3,BigDecimal.ROUND_HALF_DOWN);
                    domain.setYearElectricity(totalElectricity.doubleValue());
                    domain.setYearEconomyElectricity(totalEconomyElectricity.doubleValue());
                    break;
                }
            }

            domain.setAvgTemperature(new BigDecimal(Double.parseDouble(avg_temperature)).setScale(3,BigDecimal.ROUND_HALF_DOWN).doubleValue());
            domain.setAvgVol(new BigDecimal(Double.parseDouble(map.get("avg_vol").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN).doubleValue());
            domain.setAvgCur(new BigDecimal(Double.parseDouble(map.get("avg_cur").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN).doubleValue());
            domain.setAvgBrightness(new BigDecimal(Double.parseDouble(map.get("avg_Brightness").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN).doubleValue());
            domain.setAvgPower(new BigDecimal(Double.parseDouble(map.get("current_apparent_power").toString())).setScale(3,BigDecimal.ROUND_HALF_DOWN).doubleValue());
            stName = "sys_company_code";
            if (StringUtil.isNotEmpty(map.get(stName))) {
                domain.setSysCompanyCode(map.get(stName).toString());
                domain.setSysOrgCode(map.get(stName).toString());
            }
            dataList.add(domain);
        }

        batchSave(dataList);
    }

    @Transactional
    public void batchSave(List<GlrLightData> list) {
        for (int i = 0; i < list.size(); i++) {
            baseMapper.insert(list.get(i));
        }
    }

    /**
     *
     * @param command_timea
     * @param command_timeb
     * @param bright_valuea
     * @param current_apparent_power
     * @param rated_power
     * @return
     */
    private Map<String, Object> computePower(String command_timea, String command_timeb, String bright_valuea, String current_apparent_power, String rated_power) {
        BigDecimal b_bright_valuea = new BigDecimal(bright_valuea + "").setScale(3,BigDecimal.ROUND_HALF_DOWN);
        Map<String, Object> map = new HashMap<>();
        if (b_bright_valuea.doubleValue() == 0) {
            map.put("b_jieyue", 0);
            map.put("b_shiji", 0);
            return map;
        }
        BigDecimal b_rated_power = new BigDecimal(rated_power + "").setScale(3,BigDecimal.ROUND_HALF_DOWN);
        BigDecimal b_current_apparent_power = new BigDecimal(current_apparent_power + "").setScale(3,BigDecimal.ROUND_HALF_DOWN);
        //把当前时间和要比较的时间转换为Date类型，目的在于得到这两个时间的毫秒值
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
        Date d_command_timeb = null;
        Date d_command_timea = null;
        try {
            d_command_timeb = sdf.parse(command_timeb);
            d_command_timea = sdf.parse(command_timea);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //获得这两个时间的毫秒值后进行处理(因为我的需求不需要处理时间大小，所以此处没有处理，可以判断一下哪个大就用哪个作为减数。)
        long diff = d_command_timeb.getTime() - d_command_timea.getTime();
        //此处用毫秒值除以分钟再除以毫秒既得两个时间相差的分钟数
        long minute = diff / 60 / 1000;
        BigDecimal b_minute = new BigDecimal((double) minute / 60 + "");
        if (b_minute.intValue() < 0) {
            b_minute = b_minute.add(new BigDecimal(24));
        }
        BigDecimal b_zong = b_rated_power.multiply(b_minute).divide(new BigDecimal(1000)).setScale(3,BigDecimal.ROUND_HALF_DOWN);
        BigDecimal b_shiji = b_current_apparent_power.multiply(b_minute).divide(new BigDecimal(1000)).setScale(3,BigDecimal.ROUND_HALF_DOWN);
        //BigDecimal b_shiji = b_zong.multiply(b_zong).divide(new BigDecimal(100));

        if (b_shiji.doubleValue() != 0) {
            BigDecimal b_jieyue = b_zong.subtract(b_shiji);
            map.put("b_jieyue", b_jieyue);
        } else {
            map.put("b_jieyue", 0);
        }
        map.put("b_shiji", b_shiji);
        return map;
    }


    /**
     * 计算时间
     * @param day
     * @param typeb
     * @param minute
     * @return
     */
    public static String addDateMinut(String day, String typeb,int minute){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = null;
        try {
            date = format.parse("2019-01-10 "+day+":10");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        if (date == null)
            return "";
        //System.out.println("front:" + format.format(date)); //显示输入的日期
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        //cal.add(Calendar.HOUR, hour);// 24小时制
        //00提前 01延迟
        if ("00".equals(typeb)){
            cal.add(Calendar.MINUTE, - minute);
        }
        if ("01".equals(typeb)){
            cal.add(Calendar.MINUTE, minute);
        }


        date = cal.getTime();
        //System.out.println("after:" + format.format(date));  //显示更新后的日期
        cal = null;
        return format.format(date).substring(10,16);

    }

    public List<Map<String, Object>> doGetPowerReportListById(String ligCode, String gataWayCode, String year,String month,int type){

        String orgCodesql =" ";
        String userType= SysUtil.getUserInfo().getUserType();
        if(!userType.equals(SysConstant.SYS_USER_TYPE_ADMIN)&&!userType.equals(SysConstant.SYS_USER_TYPE_0)){
            orgCodesql=orgCodesql+"  and sys_company_code='" + SysUtil.getUserInfo().getSysCompanyCode() + "'";
        }
        String create_date1="%Y-%m-%d";
        String create_date2="%Y-%m";
        String create_date3=year+"-"+month;
        if ("00".equals(month)){
            create_date1="%Y-%m";
            create_date2="%Y";
            create_date3=year;
            month="12";
        }

        String stType="AVG";
        if (11==type) stType="AVG";
        if (12==type) stType="SUM";

        String sql = "select \n" +
                "ifnull("+stType+"(day_Electricity),0) as day_Electricity,\n" +
                "ifnull("+stType+"(day_economy_Electricity),0) as economy_Electricity,\n" +
                "ifnull("+stType+"(avg_vol),0) as avg_vol,\n" +
                "ifnull("+stType+"(avg_cur),0) as avg_cur,\n" +
                "ifnull("+stType+"(avg_brightness),0) as avg_brightness,\n" +
                "ifnull("+stType+"(avg_power),0) as avg_power,\n" +
                "ifnull("+stType+"(avg_Temperature),0) as avg_Temperature,\n" +
                "date_format(create_time, '"+create_date1+"') as create_date11 from glr_light_data \n" +
                "where 1=1   ";
        sql=sql+orgCodesql;
        if (StringUtil.isNotEmpty(ligCode)){
            ligCode=ligCode.replace("*","");
            sql=sql+" and lig_code like '%"+ ligCode + "%'";
        }
        if (StringUtil.isNotEmpty(gataWayCode)){
            gataWayCode=gataWayCode.replace("*","");
            sql=sql+" and gata_way_code like '%"+ gataWayCode + "%'";
        }
        sql=sql+"and date_format(create_time, '"+create_date2+"')='" + create_date3+ "'";



        sql=sql+" group by create_date11";

        List<Map<String, Object>> list = glrVideoYsMapper.doGetListForJdbc(sql);
        return list;
    }

    public List<Map<String, Object>> doGetPowerReportListById1(String ligCode, String gataWayCode, String yearMonth){
        yearMonth=yearMonth.substring(1,8);
        String year="";
        String month="";

        String[] sts=yearMonth.split("-");
        if (sts.length==2){
            year=sts[0];
            month=sts[1];
        }else{
            return null;
        }

        String orgCodesql =" ";
        String userType= SysUtil.getUserInfo().getUserType();
        if(!userType.equals(SysConstant.SYS_USER_TYPE_ADMIN)&&!userType.equals(SysConstant.SYS_USER_TYPE_0)){
            orgCodesql=orgCodesql+"  and glr_light_data.sys_company_code='" + SysUtil.getUserInfo().getSysCompanyCode() + "' ";
        }


        String create_date1="%Y-%m";
        String create_date2="%Y-%m";
        String create_date3=yearMonth;

        String sqlAll = "select \n" +
                "glr_light_data.gata_way_code as gata_way_code,\n" +
                "glr_gateway.gateway_name as gateway_name,\n" +
                "'"+create_date3+"' as create_date11\n"+
                "from glr_light_data \n" +
                "left join glr_gateway on glr_gateway.gate_way_code=glr_light_data.gata_way_code\n" +
                "where 1=1    ";
        sqlAll=sqlAll+orgCodesql;

        String sql = "select \n" +
                "glr_light_data.gata_way_code as gata_way_code,\n"+
                "glr_gateway.gateway_name as gateway_name,\n"+
                "ifnull(sum(day_electricity),0) as day_electricity,\n" +
                "ifnull(sum(day_economy_electricity),0) as day_economy_electricity,\n" +
                "ifnull(AVG(avg_vol),0) as avg_vol,\n" +
                "ifnull(AVG(avg_cur),0) as avg_cur,\n" +
                "ifnull(AVG(avg_brightness),0) as avg_brightness,\n" +
                "ifnull(AVG(avg_power),0) as avg_power,\n" +
                "ifnull(AVG(avg_Temperature),0) as avg_Temperature,\n" +
                "date_format(glr_light_data.create_time, '"+create_date1+"') as create_date11 " +
                "from glr_light_data \n" +
                "left join glr_gateway on glr_gateway.gate_way_code=glr_light_data.gata_way_code\n"+
                "where 1=1   "+
                "and date_format(glr_light_data.create_time, '"+create_date2+"')='" + create_date3+ "'\n";
        sql=sql+orgCodesql;

        String sql1="select \n" +
                "gata_way_code as gata_way_code,\n" +
                "ifnull(sum(day_electricity),0) as day_electricity,\n" +
                "ifnull(sum(day_economy_electricity),0) as day_economy_electricity,\n" +
                "date_format(create_time, '%Y-%m') as create_date11 \n" +
                "from glr_light_data \n" +
                "where 1=1 and date_format(create_time, '%Y-%m')<='"+year+"-"+month+"' \n" +
                "and date_format(create_time, '%Y-%m')>='"+year+"-01' \n";
        sql1=sql1+orgCodesql;

        if (StringUtil.isNotEmpty(ligCode)){
            ligCode=ligCode.replace("*","");
            sqlAll=sqlAll+" and glr_light_data.lig_code like '%"+ ligCode + "%'\n";
            sql=sql+" and glr_light_data.lig_code like '%"+ ligCode + "%'\n";
            sql1=sql1+" and glr_light_data.lig_code like '%"+ ligCode + "%'\n";
        }
        if (StringUtil.isNotEmpty(gataWayCode)){
            gataWayCode=gataWayCode.replace("*","");
            sqlAll=sqlAll+" and glr_light_data.gata_way_code like '%"+ gataWayCode + "%'\n";
            sql=sql+" and glr_light_data.gata_way_code like '%"+ gataWayCode + "%'\n";
            sql1=sql1+" and glr_light_data.gata_way_code like '%"+ gataWayCode + "%'\n";
        }

        sqlAll=sqlAll+" group by glr_light_data.gata_way_code\n" +
                      " order by glr_light_data.gata_way_code";

        sql=sql+" group by create_date11,glr_light_data.gata_way_code\n";

        sql1=sql1+" group by create_date11,gata_way_code";

        List<Map<String, Object>> listAll = glrVideoYsMapper.doGetListForJdbc(sqlAll);
        List<Map<String, Object>> list = glrVideoYsMapper.doGetListForJdbc(sql);
        List<Map<String, Object>> list1 = glrVideoYsMapper.doGetListForJdbc(sql1);

        List<Map<String, Object>> dataList= new LinkedList<Map<String, Object>>();
        for (Map<String, Object> map : listAll){
            Map<String, Object> data = new HashMap<String, Object>();
            data.put("gata_way_code",map.get("gata_way_code").toString());
            data.put("gateway_name",map.get("gateway_name").toString());
            data.put("create_date11",map.get("create_date11").toString());

            boolean bfind=false;
            for (Map<String, Object> map1 : list){
                if (map.get("gata_way_code").toString().equals(map1.get("gata_way_code").toString())){

                    data.put("day_electricity",round(Double.parseDouble(map1.get("day_electricity").toString())));
                    data.put("day_economy_electricity",round(Double.parseDouble(map1.get("day_economy_electricity").toString())));
                    data.put("avg_vol",round(Double.parseDouble(map1.get("avg_vol").toString())));
                    data.put("avg_cur",round(Double.parseDouble(map1.get("avg_cur").toString())));
                    data.put("avg_brightness",round(Double.parseDouble(map1.get("avg_brightness").toString())));
                    data.put("avg_power",round(Double.parseDouble(map1.get("avg_power").toString())));
                    data.put("avg_temperature",round(Double.parseDouble(map1.get("avg_Temperature").toString())));
                    bfind=true;
                    break;
                }
            }
            if (!bfind){
                data.put("day_electricity",0);
                data.put("day_economy_electricity",0);
                data.put("avg_vol",0);
                data.put("avg_cur",0);
                data.put("avg_brightness",0);
                data.put("avg_power",0);
                data.put("avg_temperature",0);
            }


            Double total_day_electricity=0.0;
            Double total_day_economy_electricity=0.0;
            for (Map<String, Object> map1 : list1){
                if (data.get("gata_way_code").equals(map1.get("gata_way_code").toString())){
                    Long lcurrentMonth=Long.parseLong(data.get("create_date11").toString().replace("-",""));
                    Long lcomputeMonth=Long.parseLong(map1.get("create_date11").toString().replace("-",""));
                    if (lcurrentMonth>=lcomputeMonth){
                        total_day_electricity=total_day_electricity+round(Double.parseDouble(map1.get("day_electricity").toString()));
                        total_day_economy_electricity=total_day_economy_electricity+round(Double.parseDouble(map1.get("day_economy_electricity").toString()));
                    }
                }
            }
            data.put("total_day_electricity",total_day_electricity);
            data.put("total_day_economy_electricity",total_day_economy_electricity);

            dataList.add(data);
        }
        return dataList;
    }
}
